﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Data.Odbc;
using System.Data;
using System.Web.Caching;

namespace FaithCMS
{
    public class DatabaseManipulation
    {
        //Connection string used to connect to the Sql Server database
        private static string connString;

        /// <summary>
        /// Pull the navigation links from the database
        /// </summary>
        /// <returns>DataSet containing the links</returns>
        public static DataSet CallLinkProc()
        {
            //DataSet to store database results
            DataSet linkData = new DataSet("links4");

            //Initialize the connection string
            connString = WebConfiguration.ConnectionString;

            //Initialize the connection
            OdbcConnection conn = new OdbcConnection(connString);

            //Open the connection
            conn.Open();

            //Initialize the command object
            OdbcCommand comm = new OdbcCommand("CALL GetNavLinks();", conn);
            comm.CommandType = CommandType.StoredProcedure;

            //Initialize the data adapter with the command object
            OdbcDataAdapter dataAdapter = new OdbcDataAdapter(comm);

            //Fill the dataset
            dataAdapter.Fill(linkData, "links");

            //Return the dataset
            return linkData;
        }

        /// <summary>
        /// Adds article to the database
        /// </summary>
        /// <param name="title">article title</param>
        /// <param name="author">article author</param>
        /// <param name="pubDate">article publish date</param>
        /// <param name="text">article text</param>
        public static void CallAddArticle(string title, string author, string pubDate, string text)
        {
            //Initialize the dataset
            DataSet ds = new DataSet("article");

            //Initialize the connection string
            connString = WebConfiguration.ConnectionString;

            //Initialize the connection
            OdbcConnection conn = new OdbcConnection(connString);

            //Open the connection
            conn.Open();

            //Initialize the command object
            OdbcCommand comm = new OdbcCommand("CALL AddArticle(?,?,?,?,?)", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.Add("id", OdbcType.Int);
            comm.Parameters.Add("title", title);
            comm.Parameters.Add("author", author);
            comm.Parameters.Add("date", pubDate);
            comm.Parameters.Add("text", text);

            //Initialize the data adapter with the command object
            OdbcDataAdapter da = new OdbcDataAdapter(comm);

            //Fill the dataset
            da.Fill(ds, "articles");

            //Close the connection
            conn.Close();
        }
    }
}